import openpyxl
import pymysql
import random
import string
from datetime import datetime, timedelta


# 生成随机数据的辅助函数
def generate_random_data():
    idcard = ''.join(random.choices(string.digits, k=18))
    username = ''.join(random.choices(string.ascii_lowercase, k=8))
    realname = ''.join(random.choices(string.ascii_lowercase, k=10)).capitalize()
    pwd = ''.join(random.choices(string.ascii_letters + string.digits, k=12))
    telphone = ''.join(random.choices(string.digits, k=11))
    email = f"{username}@example.com"
    age = random.randint(18, 60)
    sex = random.choice(['Male', 'Female'])
    address = ''.join(random.choices(string.ascii_lowercase + ' ', k=50)).capitalize()
    hiredate = (datetime.now() - timedelta(days=random.randint(0, 365 * 10))).strftime('%Y-%m-%d')
    sal = round(random.uniform(3000, 20000), 2)
    job = ''.join(random.choices(string.ascii_lowercase, k=10)).capitalize()
    company = ''.join(random.choices(string.ascii_lowercase, k=10)).capitalize()
    return [idcard, username, realname, pwd, telphone, email, age, sex, address, hiredate, sal, job, company]


# 1. 使用openpyxl在Excel中写入10列2000行的数据
def write_to_excel(file_path, rows, cols):
    workbook = openpyxl.Workbook()
    sheet = workbook.active

    # 写入表头
    headers = ["idcard", "username", "realname", "pwd", "telphone", "email", "age", "sex", "address", "hiredate", "sal",
               "job", "company"]
    for col, header in enumerate(headers, start=1):
        sheet.cell(row=1, column=col, value=header)

    # 写入数据
    for row in range(2, rows + 2):
        data = generate_random_data()
        for col, value in enumerate(data, start=1):
            sheet.cell(row=row, column=col, value=value)

    workbook.save(file_path)
    print(f"数据已成功写入 {file_path}")


# 2. 使用openpyxl读取Excel中的数据
def read_from_excel(file_path):
    workbook = openpyxl.load_workbook(file_path)
    sheet = workbook.active
    data = []
    for row in range(2, sheet.max_row + 1):
        row_data = [sheet.cell(row=row, column=col).value for col in range(1, sheet.max_column + 1)]
        data.append(row_data)
    return data


# 3. 使用pymysql将读取的数据写入到MySQL数据库中
def write_to_database(data, db_config):
    connection = pymysql.connect(
        host=db_config['host'],
        user=db_config['user'],
        password=db_config['password'],
        database=db_config['database'],
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    try:
        with connection.cursor() as cursor:
            # 创建表
            create_table_query = """
            CREATE TABLE IF NOT EXISTS `user_info` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `idcard` VARCHAR(50),
                `username` VARCHAR(50),
                `realname` VARCHAR(50),
                `pwd` VARCHAR(50),
                `telphone` VARCHAR(12),
                `email` VARCHAR(100),
                `age` INT,
                `sex` VARCHAR(20),
                `address` VARCHAR(200),
                `hiredate` DATE,
                `sal` DOUBLE(9,2),
                `job` VARCHAR(100),
                `company` VARCHAR(100)
            )
            """
            cursor.execute(create_table_query)

            # 插入数据
            insert_query = """
            INSERT INTO `user_info` (`idcard`, `username`, `realname`, `pwd`, `telphone`, `email`, `age`, `sex`, `address`, `hiredate`, `sal`, `job`, `company`)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            cursor.executemany(insert_query, data)
            print(f"实际插入的行数: {cursor.rowcount}")
        connection.commit()
        print("数据已成功写入数据库")
    finally:
        connection.close()


# 创建数据库
def create_database(db_config):
    connection = pymysql.connect(
        host=db_config['host'],
        user=db_config['user'],
        password=db_config['password'],
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    try:
        with connection.cursor() as cursor:
            # 创建数据库
            create_database_query = f"CREATE DATABASE IF NOT EXISTS {db_config['database']}"
            cursor.execute(create_database_query)
            connection.commit()
            print(f"数据库 {db_config['database']} 已创建")
            return connection

    except Exception as e:
        print(f"创建数据库失败: {e}")
        connection.close()
        return None
    finally:
        connection.close()
        return None


if __name__ == '__main__':
    file_path = "output.xlsx"
    rows = 2000
    cols = 10

    # 写入Excel
    write_to_excel(file_path, rows, cols)

    # 读取Excel
    data = read_from_excel(file_path)

    # 数据库配置
    db_config = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'user_info'
    }
    # 创建数据库
    create_database(db_config)
    # 写入数据库
    write_to_database(data, db_config)
